package com.ynr.voucher.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;

public class ExcelReader {

	private static final Logger logger = LoggerFactory.getLogger(ExcelReader.class);
	
	public static List<Map<String, String>> parseBankStatement(String filePath, Map<String, Object> bankTitle) {
		List<Map<String, String>> bankStamentContent = Lists.newArrayList();
		XSSFWorkbook workbook= null;
		try{
			InputStream iStream = new FileInputStream(filePath);
			workbook = new XSSFWorkbook(iStream);
			XSSFSheet xssfSheet = workbook.getSheetAt(0);
			XSSFRow row = xssfSheet.getRow(0);
			Map<Integer, String> titles = new HashMap<>();
			for(int cellNum =0; cellNum <= row.getLastCellNum(); cellNum ++) {
				if(row.getCell(cellNum) != null) {
					titles.put(cellNum, row.getCell(cellNum).toString());
				}
			}
			Map<Integer, String> specifiedRowMap = new HashMap<>();
			for(Entry<Integer, String> entry : titles.entrySet()) {
				for(Entry<String, Object> e : bankTitle.entrySet()) {
					if(entry.getValue().equals((String)e.getValue())) {
						specifiedRowMap.put(entry.getKey(), e.getKey());
					}
				}
			}
			for (int runNum = 1; runNum <= xssfSheet.getLastRowNum(); runNum++) {
				XSSFRow row_ = xssfSheet.getRow(runNum);
				Map<String, String> rowContent = new HashMap<>();
				for(Entry<Integer, String> rowEntry : specifiedRowMap.entrySet()) {
					row_.getCell(rowEntry.getKey()).setCellType(Cell.CELL_TYPE_STRING);
					String value = row_.getCell(rowEntry.getKey()).getStringCellValue()==null?"":row_.getCell(rowEntry.getKey()).getStringCellValue();
					rowContent.put(rowEntry.getValue(), value);
				}
				bankStamentContent.add(rowContent);
			}
		} catch(Exception e) {
			logger.error("ExcelReader read method throws Exception : {}", e.toString());
		} finally {
			if(workbook != null) {
				try {
					workbook.close();
				} catch (IOException e) {
					logger.error("close workbook exception.");
				}
			}
		}
		return bankStamentContent;
	}

	public static List<Map<String, Object>> parseSalaryExcel(String filePath) {
		String[] titles = {"employee_nature", "phone", "name", "wage", "endowment_person", "unemployment_person", "medical_person", "house_provident_person", "endowment_company", "unemployment_company", "medical_company", "injury_company", "birth_company", "house_provident_company", "tax", "real_wage", "type", "department", "id_num"};
		List<String> titleList = Arrays.asList(titles);
		List<Map<String, Object>> salaryContent = Lists.newArrayList();
		XSSFWorkbook workbook= null;
		try{
			InputStream iStream = new FileInputStream(filePath);
			workbook = new XSSFWorkbook(iStream);
			XSSFSheet xssfSheet = workbook.getSheetAt(0);
			for(int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow row = xssfSheet.getRow(rowNum);
				Map<String, Object> item = new HashMap<>();
				for(int cellNum=0; cellNum<row.getLastCellNum(); cellNum++) {
					row.getCell(cellNum).setCellType(Cell.CELL_TYPE_STRING);
					String value = row.getCell(cellNum).getStringCellValue();
					DecimalFormat df = new DecimalFormat("#0.00");
					if(value != null && !value.equals("")) {
						if(!titleList.get(cellNum).equals("employee_nature") && !titleList.get(cellNum).equals("phone")
								&& !titleList.get(cellNum).equals("name") && !titleList.get(cellNum).equals("type")
								&& !titleList.get(cellNum).equals("department") && !titleList.get(cellNum).equals("id_num")) {
							value = String.valueOf(df.format(Double.valueOf(value)));
						}
					}
					item.put(titleList.get(cellNum), value);
				}
				salaryContent.add(item);
			}
		} catch(Exception e) {
			logger.error("ExcelReader read method throws Exception : {}", e.toString());
		} finally {
			if(workbook != null) {
				try {
					workbook.close();
				} catch (IOException e) {
					logger.error("close workbook exception.");
				}
			}
		}
		return salaryContent;
	}
	
	public static List<Map<String, Object>> parseSalesExcel(String filePath, String companyName) {
		List<Map<String, Object>> salesList = Lists.newArrayList();
		XSSFWorkbook workbook= null;
		try{
			InputStream iStream = new FileInputStream(filePath);
			workbook = new XSSFWorkbook(iStream);
			XSSFSheet xssfSheet = workbook.getSheetAt(0);
			int ptfpBeginRow = 0;
			int ptfpEndRow = 0;
			int zyfpBeginRow = 0;
			int zyfpEndRow = 0;
			for(int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow row = xssfSheet.getRow(rowNum);
				if(row == null || row.getCell(0) == null || row.getCell(0).getStringCellValue().trim().equals("")) {
					continue;
				}
				row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
				String value = row.getCell(0).getStringCellValue();
				if(value.equals("发票类别：专用发票")) {
					zyfpBeginRow = rowNum;
				}
				if(value.equals("发票类别：普通发票")) {
					ptfpBeginRow = rowNum;
				}
				if(value.startsWith("份数")) {
					if(zyfpBeginRow>0&&ptfpBeginRow==0) {
						zyfpEndRow = rowNum;
					}
					if(zyfpBeginRow==0&&ptfpBeginRow>0) {
						ptfpEndRow = rowNum;
					}
					if(zyfpBeginRow>0&&ptfpBeginRow>0) {
						if(rowNum > zyfpBeginRow && rowNum > ptfpBeginRow) {
							if(zyfpBeginRow > ptfpBeginRow) {
								zyfpEndRow = rowNum;
							} else {
								ptfpEndRow = rowNum;
							}
						} else if(rowNum > zyfpBeginRow && rowNum < ptfpBeginRow) {
							zyfpEndRow = rowNum;
						} else if(rowNum < zyfpBeginRow && rowNum > ptfpBeginRow) {
							ptfpEndRow = rowNum;
						}
					}
				}
			}
			//System.out.println("专用发票开始行：" + zyfpBeginRow + ",专用发票结束行：" + zyfpEndRow + ",普通发票开始行：" + ptfpBeginRow + ",普通发票结束行:" + ptfpEndRow);
			if(zyfpBeginRow > 0) {
				readInvoice(zyfpBeginRow, zyfpEndRow, xssfSheet, salesList, companyName, "01");
			}
			if(ptfpBeginRow > 0) {
				readInvoice(ptfpBeginRow, ptfpEndRow, xssfSheet, salesList, companyName, "04");
			}
		} catch(Exception e) {
			System.out.println(e.toString());
			logger.error("ExcelReader read method throws Exception : {}", e.toString());
		} finally {
			if(workbook != null) {
				try {
					workbook.close();
				} catch (IOException e) {
					logger.error("close workbook exception.");
				}
			}
		}
		return salesList;
	}
	
	private static void readInvoice(int beginRow, int endRow, XSSFSheet xssfSheet, 
			List<Map<String, Object>> salesList, String companyName, String fplx) {
		Map<String, Object> invoiceMap = new HashMap<>();
		Map<String, Object> invoiceBasicMap = new HashMap<>();
		List<Map<String, Object>> invoiceItemList = Lists.newArrayList();
		for(int rowNum = (beginRow+2); rowNum < endRow-1; rowNum++) {
			XSSFRow row = xssfSheet.getRow(rowNum);
			if(row.getCell(0) != null && !row.getCell(0).getStringCellValue().equals("")) {
				if(!invoiceBasicMap.isEmpty() && !invoiceItemList.isEmpty()) {
					double sumamount = 0.0;
					double goodsamount = 0.0;
					double taxamount = 0.0;
					for(Map<String, Object> item : invoiceItemList) {
						goodsamount += Double.valueOf((String)item.get("priceSum"));
						taxamount += Double.valueOf((String)item.get("taxSum"));
					}
					sumamount = goodsamount+taxamount;
					invoiceBasicMap.put("taxamount", ""+taxamount);
					invoiceBasicMap.put("goodsamount", ""+goodsamount);
					invoiceBasicMap.put("sumamount", ""+sumamount);
					invoiceMap.putAll(invoiceBasicMap);
					invoiceMap.put("goodsData", invoiceItemList);
					salesList.add(invoiceMap);
					invoiceBasicMap = new HashMap<>();
					invoiceItemList = Lists.newArrayList();
				}
				invoiceBasicMap.put("fplx", fplx);
				invoiceBasicMap.put("fpdm", row.getCell(0).getStringCellValue());
				invoiceBasicMap.put("fphm", row.getCell(1).getStringCellValue());
				invoiceBasicMap.put("kprq", row.getCell(6).getStringCellValue());
				invoiceBasicMap.put("xfMc", companyName);
				invoiceBasicMap.put("fplxName", "增值税"+(fplx.equals("01")?"专用":"普通")+"发票");
				invoiceBasicMap.put("sfMc", "");
				invoiceBasicMap.put("sfCode", "");
				invoiceBasicMap.put("xfNsrsbh", "");
				invoiceBasicMap.put("xfContact", "");
				invoiceBasicMap.put("xfBank", "");
				invoiceBasicMap.put("gfMc", row.getCell(2).getStringCellValue());
				invoiceBasicMap.put("gfNsrsbh", row.getCell(3).getStringCellValue());
				invoiceBasicMap.put("gfContact", row.getCell(5).getStringCellValue());
				invoiceBasicMap.put("gfBank", row.getCell(4).getStringCellValue());
				invoiceBasicMap.put("code", "");
				invoiceBasicMap.put("num", "");
				invoiceBasicMap.put("del", row.getCell(17).getStringCellValue().equals("作废发票")?"是":"否");
				invoiceBasicMap.put("taxamount", "");
				invoiceBasicMap.put("remark", "");
				invoiceBasicMap.put("isGoodsList", "");
				invoiceBasicMap.put("queryCount", "");
				invoiceBasicMap.put("updateTime", "");
				
				Map<String, Object> itemMap = new HashMap<>();
				itemMap.put("name", row.getCell(9).getStringCellValue());
				itemMap.put("spec", row.getCell(10).getStringCellValue());
				itemMap.put("unit", row.getCell(11).getStringCellValue());
				itemMap.put("amount", row.getCell(12).getStringCellValue());
				itemMap.put("priceUnit", row.getCell(13).getStringCellValue());
				itemMap.put("priceSum", row.getCell(14).getStringCellValue());
				itemMap.put("taxRate", row.getCell(15).getStringCellValue());
				itemMap.put("taxSum", row.getCell(16).getStringCellValue());
				invoiceItemList.add(itemMap);
			} else {
				if(row.getCell(9).getStringCellValue().equals("小计")) continue;
				Map<String, Object> itemMap = new HashMap<>();
				itemMap.put("name", row.getCell(9).getStringCellValue());
				itemMap.put("spec", row.getCell(10).getStringCellValue());
				itemMap.put("unit", row.getCell(11).getStringCellValue());
				itemMap.put("amount", row.getCell(12).getStringCellValue());
				itemMap.put("priceUnit", row.getCell(13).getStringCellValue());
				itemMap.put("priceSum", row.getCell(14).getStringCellValue());
				itemMap.put("taxRate", row.getCell(15).getStringCellValue());
				itemMap.put("taxSum", row.getCell(16).getStringCellValue());
				invoiceItemList.add(itemMap);
			}
		}
	}
	
	public static void main(String[] args) {
		/*Map<String, Object> bankTitle = new HashMap<>();
		bankTitle.put("trade_time", "交易时间");
		bankTitle.put("income", "收入");
		bankTitle.put("cost", "支出");
		bankTitle.put("balance", "余额");
		bankTitle.put("pay_account_name", "对方户名");
		bankTitle.put("abstract_", "摘要");
		bankTitle.put("purpose", "用途");
		bankTitle.put("remark", "备注");
		bankTitle.put("backup", "备用");
		
		parseBankStatement("D:\\bankstatement.xlsx", bankTitle);*/
		System.out.println(JSON.toJSONString(parseSalesExcel("D:\\航天金税盘样式.xlsx", "上海拓佑财务咨询有限公司")));
		
	}
	
}
